CREATE DATABASE BusTicket
GO

USE BusTicket
GO

CREATE TABLE GroupAccounts
(
	IdGroup INT IDENTITY(1,1) PRIMARY KEY,
	NameGroup NVARCHAR(60) NOT NULL,
	[Description] NVARCHAR(Max)
)
GO

CREATE TABLE Employee
(
	IdEmployee INT IDENTITY(1,1) PRIMARY KEY,
	IdGroup INT FOREIGN KEY REFERENCES GroupAccounts(IdGroup),
	LoginName NVARCHAR(60) NOT NULL,
	[Password] NVARCHAR(60) NOT NULL,
	FullName NVARCHAR(60) NOT NULL,
	Age INT,
	Sex INT,
	[Address] NVARCHAR(60) NOT NULL,
	Phone NVARCHAR(15) NOT NULL,
	Email NVARCHAR(60) NOT NULL,
	Qualification NVARCHAR(60) NOT NULL,
	Create_Date DATETIME
)
GO


CREATE TABLE Customers
(
	IdCustomer INT IDENTITY(1,1) PRIMARY KEY,
	FullName NVARCHAR(60) NOT NULL,
	Age INT,
	Sex INT,
	[Address] NVARCHAR(60) NOT NULL,
	Phone NVARCHAR(15) NOT NULL,
	Email NVARCHAR(60) NOT NULL,
	IndentityCard INT,
)
GO

CREATE TABLE BusType
(
	IDBusType INT IDENTITY(1,1) PRIMARY KEY,
	Name NVARCHAR(60) NOT NULL,
	TotalSeats INT,
	Price INT,
	[Description] NVARCHAR(Max) NOT NULL,
)
GO

CREATE TABLE TravelPlan
(
	IdLocation INT IDENTITY(1,1) PRIMARY KEY,
	StartLocation NVARCHAR(60) NOT NULL,
	EndLocation NVARCHAR(60) NOT NULL,
	DateStart NVARCHAR(60) NOT NULL,
	TimePlan NVARCHAR(60) NOT NULL
)
GO

CREATE TABLE BusCar
(
	IdBusCar INT IDENTITY(1,1) PRIMARY KEY,
	IDBusType INT FOREIGN KEY REFERENCES BusType(IDBusType),
	IdLocation INT FOREIGN KEY REFERENCES TravelPlan(IdLocation),
	BusNo NVARCHAR(15) NOT NULL,
	SeatsUsed NVARCHAR(200) NOT NULL,
	[Status] INT
)
GO


CREATE TABLE BusTicket
(
	IDBusTicket INT IDENTITY(1,1) PRIMARY KEY,
	IdEmployee INT FOREIGN KEY REFERENCES Employee(IdEmployee),
	IdCustomer INT FOREIGN KEY REFERENCES Customers(IdCustomer) ,
	Amount INT,
	[Status] INT
)
GO

CREATE TABLE BusTicketDetail
(
	IdBTD INT IDENTITY(1,1) PRIMARY KEY,
	IDBusTicket INT FOREIGN KEY REFERENCES BusTicket(IDBusTicket) ON DELETE CASCADE,
	IdBusCar INT FOREIGN KEY REFERENCES BusCar(IdBusCar),
	IdCustomer INT FOREIGN KEY REFERENCES Customers(IdCustomer) ,
	Price INT,
	SeatNo NVARCHAR(20) NOT NULL
)
GO
